﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WF_Business;

namespace Business.PubInfo
{
    public class PubInfoManage
    {
        /// <summary>
        /// 读取信息公开数据
        /// </summary>
        /// <param name="i">显示条数</param>
        /// <param name="strBid">大类id</param>
        /// <param name="Sid">小类id</param>
        /// <param name="strWhere">其他条件</param>
        /// <returns></returns>
        public DataTable GetPubInfoList(int i, string strBid, string Sid, string strWhere)
        {
            string sql = "select * from (select id,lx,xlx,bt,to_char(sj,'yyyy-mm-dd') as tjrq from UT_XWFB  where fbzt=1 ";

            if (!string.IsNullOrEmpty(strBid))
            {
                sql += string.Format(" and lx='{0}' ", strBid);
            }
            if (!string.IsNullOrEmpty(Sid))
            {
                sql += string.Format(" and xlx='{0}' ", Sid);
            }

            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += strWhere;
            }
            sql += "order by sj desc)";
            if (i > 0)
            {
                sql += string.Format(" where rownum<={0}", i);
            }
            
            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 显示一、二级导航标题
        /// </summary>
        /// <param name="strId"></param>
        /// <returns></returns>
        public DataTable GetBiaoTi(string strId) 
        {
            string sql = string.Format("select c.id,(select pro_content from ST_DICTIONARY a where a.pro_code=c.lx) lx,(select pro_content from ST_DICTIONARY a where a.pro_code=c.lx) xlx from  UT_XWFB c where fbzt=1 and c.id='{0}'", strId);
            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 根据大类获取小类型
        /// </summary>
        /// <param name="strBigClass"></param>
        /// <returns></returns>
        public DataTable GetXlByBgClass(string strBigClass)
        {
            string sql = string.Format("select * from ST_DICTIONARY a where a.p_code=(select pro_code from ST_DICTIONARY b where b.pro_name='新闻类型' and b.pro_content='{0}') order by a.pro_code", strBigClass);
            DataTable dtClass;
            SysParams.OAConnection().RunSql(sql, out dtClass);
            return dtClass;
        }

        /// <summary>
        /// 根据附加类获取所有的类型和新闻
        /// </summary>
        /// <param name="strExtClass"></param>
        /// <returns></returns>
        public DataTable GetXwByExtClass(string strExtClass)
        {
            string sql = string.Format("select distinct c.lx,c.xlx from UT_XWFB c where fbzt=1 and c.fjl='{0}'", strExtClass);
            DataTable dtClass;
            SysParams.OAConnection().RunSql(sql, out dtClass);
            return dtClass;
        }

        /// <summary>
        /// 获取内容
        /// </summary>
        /// <param name="strId"></param>
        /// <returns></returns>
        public DataTable GetContent(string strId)  
        {
            string sql = string.Format("select t.id,t.bt,t.nr,zz,hits,to_char(sj,'yyyy-mm-dd') as tjrq from UT_XWFB t where id='{0}'", strId);
            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 设置点击个数
        /// </summary>
        /// <param name="strArtId"></param>
        public void SetClickCount(string strArtId)
        {
            string sql = string.Format("update UT_XWFB set hits=hits+1 where id='{0}'", strArtId);
            SysParams.OAConnection().RunSql(sql);
        }
        
        /// <summary>
        /// 获取公示的图信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetPubTuList()
        {
            string sql = "select * from (select id,title,tupian,tu from UT_XWFB where  fbzt=1 and tu=1 order by tjrq desc) a where  rownum<=5 ";

            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 获取所有的行程记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetEvents(string strUserId)
        {
            string sql = "select (select a.user_name from st_user a where a.userid=c.userid) xm,c.* from ut_events c where 1=1";
            if(!string.IsNullOrEmpty(strUserId))
            {
                sql += string.Format(" and userid='{0}'",strUserId);
            }

            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }

        /// <summary>
        /// 获取出差情况
        /// </summary>
        /// <returns></returns>
        public DataTable GetCcqk()
        {
            string sql = "select a.* from VT_GJCH_CCSPB a where a.zt='通过'";

            DataTable dtlist;
            SysParams.OAConnection().RunSql(sql, out dtlist);
            return dtlist;
        }
    }
}
